package com.example.qxfw.qxxx.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.example.qxfw.common.entity.qx.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
@DS("qx")
public interface SumDateCommonMapper {

    @Select("select stationid, stationname, year, month, case when ((day-1)/5+1)=7 then 6 else ((day-1)/5+1) end as hou, cast(${type}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${cn}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') between '${start}' and '${end}' " +
            "group by stationid, stationname, year, month, case when ((day-1)/5+1)=7 then 6 else ((day-1)/5+1) end " +
            "order by stationid, year, month, hou ")
    List<SumRainHouData> selectHouData(@Param("type") String type, @Param("tableName") String tableName, @Param("cn") String cn, @Param("start")  String start, @Param("end")  String end);

    @Select("select stationid, stationname, year, month, case when ((day-1)/10+1)=4 then 3 else ((day-1)/10+1) end as td,cast(${type}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${cn}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') between '${start}' and '${end}' " +
            "group by  stationid, stationname, year, month, case when ((day-1)/10+1)=4 then 3 else ((day-1)/10+1) end " +
            "order by stationid, year, month, td")
    List<SumRainTdData> selectXunData(String type,@Param("tableName") String tableName,@Param("cn") String cn,@Param("start")  String start,@Param("end")  String end);

    @Select("select stationid, stationname, year, month, cast(${type}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${cn}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') between '${start}' and '${end}' " +
            "group by stationid, stationname, year, month " +
            "order by stationid, year, month")
    List<SumRainMonthData> selectMonthData(String type,@Param("tableName") String tableName,@Param("cn") String cn,@Param("start")  String start,@Param("end")  String end);

    @Select("select stationid, stationname, year, cast(${type}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${cn}' and year between '${start}' and '${end}' " +
            "group by stationid, stationname, year " +
            "order by stationid, year")
    List<SumRainYearData> selectYearData(String type,@Param("tableName") String tableName,@Param("cn") String cn,@Param("start")  String start,@Param("end")  String end);

    @Delete("delete from ${tableName} " +
            "where year='${year}' and month='${month}' and day='${day}' and stationid='${stationId}' ")
    Integer deleteDayData(@Param("tableName") String tableName,@Param("year")  String year,@Param("month")  String month,@Param("day")  String day,@Param("stationId")  String stationId);

    @Insert("insert into ${tableName} " +
            "(year,month,day,stationid,sumindicatorid,stationname,sumindicatorname,sumvalue,staticdate) " +
            "values (${data.year},${data.month},${data.day},${data.stationid},${data.sumindicatorid},${data.stationname},${data.sumindicatorname},${data.sumvalue},${data.staticdate})")
    Integer insertDayData(@Param("tableName") String tableName, SumRainDayData data);

    @Delete("delete from ${tableName} " +
            "where year='${year}' and month='${month}' and hou='${hou}' and stationid='${stationId}' ")
    Integer deleteHouData(@Param("tableName") String tableName,@Param("year")  String year,@Param("month")  String month,String hou,@Param("stationId")  String stationId);

    @Insert("insert into ${tableName} " +
            "(year,month,hou,stationid,sumindicatorid,stationname,sumindicatorname,sumvalue) " +
            "values (${data.year},${data.month},${data.hou},${data.stationid},${data.sumindicatorid},${data.stationname},${data.sumindicatorname},${data.sumvalue})")
    Integer insertHouData(@Param("tableName") String tableName, SumRainHouData data);

    @Delete("delete from ${tableName} " +
            "where year='${year}' and month='${month}' and td='${td}' and stationid='${stationId}' ")
    Integer deleteXunData(@Param("tableName") String tableName,@Param("year")  String year,@Param("month")  String month,String td,@Param("stationId")  String stationId);

    @Insert("insert into ${tableName} " +
            "(year,month,td,stationid,sumindicatorid,stationname,sumindicatorname,sumvalue) " +
            "values (${data.year},${data.month},${data.td},${data.stationid},${data.sumindicatorid},${data.stationname},${data.sumindicatorname},${data.sumvalue})")
    Integer insertXunData(@Param("tableName") String tableName,SumRainTdData data);

    @Delete("delete from ${tableName} " +
            "where year='${year}' and month='${month}' and stationid='${stationId}' ")
    Integer deleteMonthData(@Param("tableName") String tableName,@Param("year")  String year,@Param("month")  String month,@Param("stationId")  String stationId);

    @Insert("insert into ${tableName} " +
            "(year,month,stationid,sumindicatorid,stationname,sumindicatorname,sumvalue) " +
            "values (${data.year},${data.month},${data.stationid},${data.sumindicatorid},${data.stationname},${data.sumindicatorname},${data.sumvalue})")
    Integer insertMonthData(@Param("tableName") String tableName,SumRainMonthData data);

    @Delete("delete from ${tableName} " +
            "where year='${year}' and stationid='${stationId}' ")
    Integer deleteYearData(@Param("tableName") String tableName,@Param("year")  String year,@Param("stationId")  String stationId);

    @Insert("insert into ${tableName} " +
            "(year,stationid,sumindicatorid,stationname,sumindicatorname,sumvalue) " +
            "values (${data.year},${data.stationid},${data.sumindicatorid},${data.stationname},${data.sumindicatorname},${data.sumvalue})")
    Integer insertYearData(@Param("tableName") String tableName,SumRainYearData data);

    @Select("Select avg(sumvalue) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${name}' and year>='${y1}' and year<='${y2}' and stationname='${stationname}'")
    List<SumRainYearData> selectYearAvg(@Param("tableName") String tableName,@Param("name")  String name,@Param("y1")  String y1,@Param("y2")  String y2,@Param("stationname")  String stationname);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}' " +
            "order by year ")
    List<SumRainYearData> selectYear(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname,@Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' " +
            "order by year ")
    List<SumRainYearData> selectYear1(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname,@Param("stationname")  String stationname);

    @Select("select month,avg(sumvalue) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and year>='${y1}' and year<='${y2}' and stationname='${stationname}'" +
            "group by month " +
            "order by month ")
    List<SumRainMonthData> selectMonthAvgData(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("y1")  String y1, @Param("y2")  String y2, @Param("stationname")  String stationname);

    @Select("select year, month, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>=${y1} and year<=${y2} " +
            "order by year,month ")
    List<SumRainMonthData> selectMonth(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select year, month, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>=${y1} and year<=${y2} " +
            "order by year,month ")
    List<SumRainDayData> selectDay(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select month,td, avg(sumvalue) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and year>='${y1}' and year<='${y2}' and stationname='${stationname}' " +
            "group by month,td order by month")
    List<SumRainTdData> selectXunAvg(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("y1")  String y1, @Param("y2")  String y2, @Param("stationname")  String stationname);

    @Select("select year, month, td, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year between ${y1} and ${y2} " +
            "order by year,month,td")
    List<SumRainTdData> selectXun(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select month, hou, avg(sumvalue) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and year>='${y1}' and year<='${y2}' and stationname='${stationname}' " +
            "group by month,hou order by month")
    List<SumRainHouData> selectHouAvg(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("y1")  String y1, @Param("y2")  String y2, @Param("stationname")  String stationname);

    @Select("select year, month, hou, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year between ${y1} and ${y2} " +
            "order by year, month, hou ")
    List<SumRainHouData> selectHou(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select year, month,(case when ((day-1)/10+1)>=4 then 3 else (day-1)/10+1 end) as stationid, count(*) as day " +
            "from sum_rain_day_data " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and sumvalue >= 0.1 and year between ${y1} and ${y2} " +
            "group by year, month, ( CASE WHEN ( ( DAY - 1 ) / 10+1 ) >= 4 THEN 3 ELSE ( DAY - 1 ) / 10+1 END ) "  +
            "order by  month, year,stationid")
    List<SumRainDayData> selectXunDay(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1,@Param("y2")  String y2);

    @Select("select stationid, stationname, sum(sumvalue) as sumvalue " +
            "from sum_rain_month_data " +
            "where year='${year}' and month in (${month}) " +
            "group by stationid,stationname ")
    List<SumRainMonthData> selectHighWater(@Param("year")  String year,@Param("month")  String months);

    @Select("select stationid, stationname, sum(sumvalue) as sumvalue " +
            "from sum_rain_month_data " +
            "where year='${year}' and month in ('4','5','6','7','8','9')  and stationid like '5%'" +
            "group by stationid,stationname " +
            "order by sum(sumvalue) desc")
    List<SumRainMonthData> selectHighWaterAvg(@Param("year")  String year);

    @Select("select year, month, count(*) as day " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>=${y1} and year<=${y2} and sumvalue >= 0.1" +
            "group by year, month " +
            "order by year,month ")
    List<SumRainDayData> selectDayCount(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2);

    @Select("select stationid, stationname, sum(sumvalue) as sumvalue " +
            "from sum_rain_month_data " +
            "WHERE year BETWEEN ${start} AND ${end} " +
            "AND month IN ('4', '5', '6') " +
            "group by stationid,stationname " +
            "order by sum(sumvalue) desc")
    List<SumRainMonthData> selectHighWaterBefore(@Param("start") String start, @Param("end")  String end);

    @Select("select stationid, stationname, sum(sumvalue) as sumvalue " +
            "from sum_rain_month_data " +
            "WHERE year BETWEEN ${start} AND ${end} " +
            "AND month IN ('7', '8', '9') " +
            "group by stationid,stationname " +
            "order by sum(sumvalue) desc")
    List<SumRainMonthData> selectHighWaterAfter(@Param("start") String start, @Param("end")  String end);

    @Select("SELECT stationid, stationname, SUM(sumvalue) AS sumvalue " +
            "FROM sum_rain_month_data " +
            "WHERE year BETWEEN ${start} AND ${end} " +
            "AND month IN ('4', '5', '6','7', '8', '9') " +
            "GROUP BY stationid, stationname " +
            "ORDER BY sum(sumvalue) DESC")
    List<SumRainMonthData> selectHighWaterXq(@Param("start") String start, @Param("end")  String end);

    @Select("select stationid, stationname, sum(sumvalue) as sumvalue " +
            "from sum_rain_month_data " +
            "where year>='${start}' and year<='${end}' and month in ('4','5','6','7','8','9')  and stationid like '5%'" +
            "group by stationid,stationname " +
            "order by sum(sumvalue) desc")
    List<SumRainMonthData> selectHighWaterXqAvg(@Param("start")  String start, @Param("end")  String end);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}'  and month>='${m1}' and month <='${m2}' " +
            "order by year ")
    List<SumRainYearData> selectYear3Month(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2, String m1, String m2);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}'  and month='${m}' " +
            "order by year ")
    List<SumRainYearData> selectYearMonth(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2, String m);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}' and month='${m}'  and td='${tdhou}' " +
            "order by year ")
    List<SumRainYearData> selectYearTd(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2, String m, @Param("tdhou") String tdhou);

    @Select("select year, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}' and month='${m}'  and hou='${tdhou}' " +
            "order by year ")
    List<SumRainYearData> selectYearHou(@Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2, String m, @Param("tdhou") String tdhou);

    @Select("select year, cast(${optype}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and year>='${y1}' and year<='${y2}' and month>='${m1}' and month <='${m2}' and day>='${d1}' and day <='${d2}' " +
            "group by year order by year ")
    List<SumRainYearData> selectYearDay(String optype, @Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("y1")  String y1, @Param("y2")  String y2, String m1, String m2, String d1, String d2);

    @Select("select stationname, cast(${optype}(sumvalue) as numeric(18,1)) as sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') >='${start}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') <='${end}' " +
            "group by stationname order by sumvalue ")
    List<SumRainDayData> getDayData1(String optype, @Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("start")  String start, @Param("end")  String end);

    @Select("select stationname, year, month, day, sumvalue " +
            "from ${tableName} " +
            "where sumindicatorname='${sumindicatorname}' and stationname='${stationname}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') >='${start}' and to_char(year, 'FM9999')||to_char(month,'FM09')||to_char(day,'FM09') <='${end}' " +
            "order by stationname, year, month, day, sumvalue ")
    List<SumRainDayData> getDayData2(String optype, @Param("tableName") String tableName, @Param("sumindicatorname")  String sumindicatorname, @Param("stationname")  String stationname, @Param("start")  String start, @Param("end")  String end);
}
